# DML（Data Manipulation Language）

# 内嵌函数
## 编码转换
select convert('你好' using gbk)  from dual;

## 日期时间相关
### 当前事件戳
SELECT CURRENT_TIMESTAMP from dual;
### 格式化时间
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); -- 输出格式2022-05-22 09:57:59
### 日期间隔
select datediff(now(),'2022-10-19') from dual;

## 字符串相关
### 拼接字符串
SELECT CONCAT('1','2','3');
SELECT CONCAT_WS(',','1','2','3');-- 第一个参数为拼接分隔符

## 判断相关
### ifnull
select ifnull(null,1) from dual;

## UUID
select uuid() ;
select uuid_short();

## 数字计算
### 四舍五入
select round(1.999) from dual;#到个位（到零位小数）
select round(1.994,2) from dual;#到两位小数
### 聚合函数
#### 平均值
select avg(age) from t_simple;
#### 最大值
select max(age) from t_simple;
#### 最小值
select min(age) from t_simple;
#### 求和
select sum(age) from t_simple;
#### 总记录数
select count(*) from t_simple;
select count(name) from t_simple; #如果此列存在null值，不会被统计到
### 取整
#### 向下取整
select floor(1.1);
select floor(-1.1);
#### 向上取整
select ceiling(1.1);
select ceiling(-1.1);

# 查询
## 模糊查询
select *
from vue3_person where profession like '程%';
select *
from vue3_person where profession like '程___';#一个“_”代表一个随机字符
### https://dev.mysql.com/doc/refman/8.0/en/regexp.html 正则表达式匹配
select *
from vue3_person where profession regexp '^程序员[0-9]$';

## 连接查询
### 内连接
select *
from t_simple inner join user u on t_simple.name = u.name;
### 左外连接 保留左边表的所有记录（当有未与右边表匹配的记录时，右边表以null填充数据）
select *
from t_simple left join user u on t_simple.name = u.name;
### 右外连接 保留右边表的所有记录（当有未与左边表匹配的记录时，左边表以null填充数据）
select *
from t_simple right join user u on t_simple.name = u.name;

## 合并查询结果集
select *
from t_simple where id ='1'
union
select *
from t_simple where id ='2';

## group by查询
### select子句只能出现：group by子句的列名或聚合函数,但可以通过group_concat函数查询非group by子句的列名数据
### [with rollup]会在最后一行对聚合函数的列数据再次进行聚合
select age,count(*)
from t_simple group by age with rollup ;
select age,count(*),group_concat(name)
from t_simple group by age with rollup ;
### [group by]+一个或多个数字:表示按[select]子句声明的哪第几列进行聚合操作
select name
from t_simple group by 1;
select name,count(*),age
from t_simple group by 1,3;
select group_concat(name),age
from t_simple group by 2;

## [having]用于筛选数据
### 与[group by]一起使用时，条件中可为：聚合函数,[group by]子句中的列(Using aggregate-free condition(s) in HAVING clause might be inefficient，在having子句中使用非聚合条件可能低效)
select avg(age),name
from t_simple group by name having avg(age) > 1;
select avg(age),name
from t_simple group by name having name > 2;
### [having]也可当[where]使用，但性能不好
select *
from t_simple having age = 1;

# 逻辑运算符
## 异或 XOR 一个满足，一个不满足
select *
from t_simple where name is not null xor age = 1;

# 按位运算符
## 按位与
select 8 & 7 from dual;
## 按位或
select 8 | 7 from dual;
## 左移 0000 1000（8的二进制表示） 左移一位为 0001 0000（16的二进制表示）
select 8 << 1 from dual;

-- 流程控制
-- 流程控制之case
SELECT
case
	when '1' in ('2')
		then '2'
	when '2' in ('2')
		then '4'
	else '3'
end
from dual;
SELECT
case '2'
	when '1' then '2'
	when '2' then '4'
	else '3'
end
from dual;

-- 插入时如果有重复key，则更新指定字段(**如果有自增字段，且存在insert重复键，会导致自增增加,比如：自增值到了5，这次insert全是重复键数据，共4条，那下次insert时，自增值会从9开始)
#    如果多线程执行，高并发，容易导致死锁
INSERT INTO umbrellaWoman.t_simple (id, name, age, delete_flag, deleted, version, create_time, update_time)
VALUES (null, '7', 1, 9, NULL, 1, '2022-04-24 08:38:48', '2022-04-24 08:38:56'),
       (null, '7', 1, 9, NULL, 1, '2022-04-24 08:38:48', '2022-04-24 08:38:56'),
       (null, '7', 3, 9, 3, 3, '2022-04-24 08:40:23', '2022-04-24 08:40:23'),
       (null, '7', 9, 9, 8, 90, '2022-04-24 08:38:48', '2022-04-23 08:43:07')
on duplicate KEY UPDATE
	name = values (name),
	age = values (age),
	deleted = values (deleted),
	version = values (version),
	delete_flag = values (delete_flag);

# INSERT ... SELECT Statement 字段名可不一样
INSERT INTO rainMan.user (email,age)
  SELECT rainMan.t_simple.name,rainMan.t_simple.version
  FROM rainMan.t_simple WHERE rainMan.t_simple.name='2';


# https://dev.mysql.com/doc/refman/8.0/en/with.html  Recursive Common Table Expressions
#   递归查询数据，可代替Oracle的树查询start with org.brcode = '370010000'
#          CONNECT BY
#          PRIOR org.brcode = org.BLN_UP_BRCODE
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

# case 流程控制
# https://dev.mysql.com/doc/refman/5.6/en/user-variables.html
set @`condition1` = 2; #用户定义变量，断开连接后自动释放
select case @`condition1` when 1 then 2 when 2 then 3 else 4 end ;

# 多表update语句，用一个表的数据更新另一个表 https://dev.mysql.com/doc/refman/8.0/en/update.html
update vue3_person,user
set vue3_person.name = user.name
where user.id = '1';



